<?php
	require_once('config.php');
 	function connectdb($servername,$dbname,$dbuser,$dbpassword)
 	{
		$link=mysql_connect ("$servername","$dbuser","$dbpassword");
		if(!$link)
		{
			die("Could not connect to MySQL");
		}
		mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
		return $link;
 	}

	/*
	 * This function is designed to get all protocol for a site
	 *
	 */
 	function get_protocols($site_id, $db_link, &$error){
 		$sql = "SELECT site_protocol.protocol_id AS protocol_id, protocols.name AS protocol_name, protocols.port AS protocol_port, site_protocol.checking_rate as checking_rate "
				. " FROM site_protocol LEFT JOIN protocols ON site_protocol.protocol_id = protocols.id"
				. " WHERE site_id =" . $site_id;
		$result = mysql_query($sql, $db_link);
		$protocols = array();
		if(!$result){
			$error = mysql_error($link);
			return false;
		}else{
			while($row = mysql_fetch_assoc($result)){
				array_push($protocols, $row);
			}
			return $protocols;
		}
 	}
	/*
	 * This function is designed to get all checking records in a hour
	 */
 	function get_checking_records_by_minute($site_id, $protocol_id, $year, $month, $day, $hour, $dblink, &$error){
		$start_query_time = $year . "-" . $month . "-" . $day . " " . $hour . ":00:00";
		$datetime = getdate(mktime($hour+1, 0, 0, $month, $day, $year));
		$end_query_time = $datetime['year'] . "-" . $datetime['mon'] . "-" . $datetime['mday'] . " " . $datetime['hours'] . ":00:00";
		$query = "SELECT starting_time, operating_time, status, response_code, message "
				."FROM checking_records "
				."WHERE site_id = " . $site_id . " AND protocol_id= " . $protocol_id . " "
				."AND(starting_time BETWEEN '" . $start_query_time . "' AND '" . $end_query_time . "')";
		$result = mysql_query($query, $dblink);
		$records = array();
		if(!$result){
			$error = mysql_error($dblink);
			return false;
		}else{
			while($row = mysql_fetch_assoc($result)){
				array_push($records, $row);
			}
			return $records;
		}
 	}

 	/*
	 * This function is designed to get the hour summary checking record in a day
	 */
 	function get_checking_records_by_hour($site_id, $protocol_id, $year, $month, $day, $dblink, &$error){
		$start_query_time = $year . "-" . $month . "-" . $day . " " . "00:00:00";
		$end_query_time = $year . "-" . $month . "-" . $day . " " . "23:59:59";
		$query = "SELECT HOUR(starting_time) AS hour, SUM(operating_time) AS total_time "
				. "FROM checking_records "
				. "WHERE site_id = " . $site_id . " AND protocol_id= " . $protocol_id . " "
				. "AND(starting_time BETWEEN '" . $start_query_time . "' AND '" . $end_query_time . "') "
				. "GROUP BY HOUR(starting_time)";
		$result = mysql_query($query, $dblink);
		$records = array();
		if(!$result){
			$error = mysql_error($dblink);
			return false;
		}else{
			while($row = mysql_fetch_assoc($result)){
				array_push($records, $row);
			}
			return $records;
		}
 	}

 	/*
	 * This function is designed to get the day summary checking record in a month
	 */
 	function get_checking_records_by_day($site_id, $protocol_id, $year, $month, $dblink, &$error){
		$query = "SELECT DAY(starting_time) AS day, SUM(operating_time) AS total_time "
				. "FROM checking_records "
				. "WHERE site_id = " . $site_id . " AND protocol_id= " . $protocol_id . " "
				. "AND MONTH(starting_time) = $month AND YEAR(starting_time) = $year "
				. "GROUP BY DAY(starting_time)";
		$result = mysql_query($query, $dblink);
		$records = array();
		if(!$result){
			$error = mysql_error($dblink);
			return false;
		}else{
			while($row = mysql_fetch_assoc($result)){
				array_push($records, $row);
			}
			return $records;
		}
 	}

 	function close($link)
 	{
 		mysql_close($link);
 	}
?>
